﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

using System.Collections;

namespace auxiliary_proj
{
    /*
     *CLASS QUERY:
     *This class is used to handle all MySQL connections
     *The included methods will either execute a non-query (insert/update/delete)
     *or it will return a result, DataTable
     */
    public static class Query
    {
        static MySqlConnection connection;
        static MySqlCommand command;

        //Hashtables for all pairs of items used over and over
        private static Hashtable catid_desc = new Hashtable();

        private static Hashtable all_itemid_desc = new Hashtable();
        private static Hashtable unit_itemid_desc = new Hashtable();

        private static Hashtable vendorCode_desc = new Hashtable();

        private static Hashtable vendorCode_itemid = new Hashtable();

        public static Hashtable CATID_DESC
        {
            get
            {
                return catid_desc;
            }
        }

        public static Hashtable ALL_ITEMID_DESC
        {
            get
            {
                return all_itemid_desc;
            }
        }

        public static Hashtable UNIT_ITEMID_DESC
        {
            get
            {
                return unit_itemid_desc;
            }
        }

        public static Hashtable VENDORCODE_DESC
        {
            get
            {
                return vendorCode_desc;
            }
        }

        public static Hashtable VENDORCODE_ITEMID
        {
            get
            {
                return vendorCode_itemid;
            }
        }

        //takes in a query string, executes a statement that returns true if successful
        //INSERT,DELETE,UPDATE,&c
        public static bool nonQuery(string q)
        {
            connection = new MySqlConnection(Program.getConnection());
            command = new MySqlCommand(q, connection);
            bool isSuccessful = false;
            Cursor.Current = Cursors.WaitCursor;
            try
            {
                connection.Open();
                try
                {
                    command.ExecuteNonQuery();
                    isSuccessful = true;
                }
                catch (MySqlException e)
                {
                    MessageBox.Show("Query nonQuery 1 - " + e.Message);
                }
            }
            catch (Exception e)
            {
                MessageBox.Show("Query nonQuery 2 - " + e.Message);
            }
            finally
            {
                connection.Close();
            }
            Cursor.Current = Cursors.Default;
            return isSuccessful;            
        }

        
        /// <summary>
        /// takes in a query string, selects data from DB, and returns a Table of the result set
        /// </summary>
        /// <param name="q"></param>
        /// <returns></returns>
        public static DataTable select(string q)
        {
            DataTable result = new DataTable();
            MySqlDataAdapter d = new MySqlDataAdapter(q, Program.getConnection());
            Cursor.Current = Cursors.WaitCursor;
            try
            {
                d.Fill(result);
            }
            catch (DataException ex) { MessageBox.Show(ex.Message); }
            Cursor.Current = Cursors.Default;
            return result;
        }


        /// <summary>
        /// VALID INPUTS: CATID_DESC, ITEMID_DESC, VENDORCODE_DESC
        /// 
        /// this takes in the name of the hashtable you want to modify
        /// depending on the parameter, the corresponding hashtable is updated
        /// 
        /// once in the correct hashtable, it determines if the hashtable
        ///     (1) doesn't contain the key -- add it, else move on
        ///     (2) contains the key, but values don't match -- update value, else move on
        ///     (3) contains key, and values match -- ignore, nothing has changed
        /// </summary>
        /// <param name="name"></param>
        public static void populateHashtable(string name, string extra)
        {
            //query catid and desc and populate hashtable
            if (name.Equals("CATID_DESC"))
            {
                DataTable res = select("SELECT cat_id, description FROM category ORDER BY description");

                //run through each row in the result
                foreach (DataRow row in res.Rows)
                {
                    //if the Hashtable doesn't contain the key, add it
                    if (!catid_desc.ContainsKey(row["cat_id"]))
                        catid_desc.Add(row["cat_id"], row["description"]);

                    //or if the key exists but the value is not the same, update it
                    else if (!catid_desc[row["cat_id"]].Equals(row["description"]))
                    {
                        catid_desc[row["cat_id"]] = row["description"];
                    }

                    else { /* the key exists, and the value is the same, do nothing */ }

                }//end foreach
            }
            else if (name.Equals("ALL_ITEMID_DESC"))
            {
                DataTable res = select("SELECT item_id, description FROM item ORDER BY description");

                //run through each row in the result
                foreach (DataRow row in res.Rows)
                {
                    //if the Hashtable doesn't contain the key, add it
                    if (!all_itemid_desc.ContainsKey(row["item_id"]))
                        all_itemid_desc.Add(row["item_id"], row["description"]);

                    //or if the key exists but the value is not the same, update it
                    else if (!all_itemid_desc[row["item_id"]].Equals(row["description"]))
                    {
                        all_itemid_desc[row["item_id"]] = row["description"];
                    }

                    else { /* the key exists, and the value is the same, do nothing */ }

                }//end foreach
            }
            else if (name.Equals("UNIT_ITEMID_DESC"))
            {
                DataTable res = select("SELECT i.item_id, i.description FROM item AS i, item_unit AS iu " + extra + "AND i.item_id = iu.item_id ORDER BY description");

                //run through each row in the result
                foreach (DataRow row in res.Rows)
                {
                    //if the Hashtable doesn't contain the key, add it
                    if (!unit_itemid_desc.ContainsKey(row["item_id"]))
                        unit_itemid_desc.Add(row["item_id"], row["description"]);

                    //or if the key exists but the value is not the same, update it
                    else if (!unit_itemid_desc[row["item_id"]].Equals(row["description"]))
                    {
                        unit_itemid_desc[row["item_id"]] = row["description"];
                    }

                    else { /* the key exists, and the value is the same, do nothing */ }

                }//end foreach
            }
            else if (name.Equals("VENDORCODE_DESC"))
            {
                DataTable res = select("SELECT vendorCode, description FROM item "+extra+"ORDER BY description");

                //run through each row in the result
                foreach (DataRow row in res.Rows)
                {
                    //if the Hashtable doesn't contain the key, add it
                    if (!vendorCode_desc.ContainsKey(row["vendorCode"]))
                        vendorCode_desc.Add(row["vendorCode"], row["description"]);

                    //or if the key exists but the value is not the same, update it
                    else if (!vendorCode_desc[row["vendorCode"]].Equals(row["description"]))
                    {
                        vendorCode_desc[row["vendorCode"]] = row["description"];
                    }

                    else { /* the key exists, and the value is the same, do nothing */ }

                }//end foreach
            }

            else if (name.Equals("VENDORCODE_ITEMID"))
            {
                DataTable res = select("SELECT vendorCode, item_id FROM item "+extra+"ORDER BY item_id");

                //run through each row in the result
                foreach (DataRow row in res.Rows)
                {
                    //if the Hashtable doesn't contain the key, add it
                    if (!vendorCode_itemid.ContainsKey(row["vendorCode"]))
                        vendorCode_itemid.Add(row["vendorCode"], row["item_id"]);

                    //or if the key exists but the value is not the same, update it
                    else if (!vendorCode_itemid[row["vendorCode"]].Equals(row["item_id"]))
                    {
                        vendorCode_itemid[row["vendorCode"]] = row["item_id"];
                    }

                    else { /* the key exists, and the value is the same, do nothing */ }

                }//end foreach
            }
            else { /*error, ignore*/ }
        }

        /// <summary>
        /// this is an extension on Hashtable
        /// basically returns key for a searched value
        /// </summary>
        /// <param name="table">bring in the hashtable you want to use</param>
        /// <param name="value">value to look up</param>
        /// <returns></returns>
        public static object getKeyByValue(Hashtable table, string value)
        {
            foreach (DictionaryEntry n in table)
            {
                if (value.Equals(n.Value.ToString()))
                {
                    //value/key pair found, return key
                    return n.Key;
                }
            }
            //no match
            return -1;
        }
    }
}
